from bean.charge_rate import ChargeRate
from bean.parking_record import ParkingRecord
# import mysql.connector  # 导入MySQL驱动:
import time
import mydb
import datetime

import numpy
import matplotlib.pyplot as plt
import matplotlib

from dateutil.relativedelta import relativedelta

# database_config = {
#     'host': 'localhost',
#     'user': 'root',
#     'password': 'root',
#     'database': 'parking'
# }

database_config = {
    'host': 'xkl265.cn',
    'user': 'user',
    'password': 'Ljy684250!',
    'database': 'parking'
}
database = mydb.MyDB(**database_config)  # 创建自定义数据库对象


def toTimeStr(timeStamp: float):
    '''
    将时间戳转换为能够赋值给数据库datetime类型的字符串
    '''
    return time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(timeStamp))


def enter_parkinglots(licence_plate: str, car_type: int, remark: str = '', entry_time=time.time()) -> bool:
    '''
    进入停车场，将停车信息写入数据库
    @param licence_plate:车牌
    @param car_type:车型
    @param remark:备注
    @param entry_time:进场时间，如果未传入，则默认使用当前时间
    @return 是否成功
    '''
    # 查询当前车型收费标准
    standard = query_current_standard(car_type)
    # 查询停车记录(如果遇到车牌重复则报错)
    if query_record_by_plate(licence_plate) != None:
        return False
    else:
        # 插入停车记录
        sql = 'INSERT INTO record(licence_plate,type,remark,entry_time,status,standard) VAlUES(%s,%s,%s,%s,%s,%s)'
        # status 停车状态，0-已离场，1-正在停车
        value = (licence_plate, car_type, remark,
                 toTimeStr(entry_time), 1, standard.standard_id)
        print(value)
        return database.update(sql, value)[0] > 0


def query_record_by_plate(licence_plate: str) -> ParkingRecord:
    '''
    根据车牌找到对应的停车信息
    '''
    sql = "SELECT * FROM record WHERE licence_plate='{}' AND status=1".format(
        licence_plate)
    result = database.query(sql)
    if len(result) > 0:
        record = result[0]
    else:
        print('查询不到该车牌对应的记录')  # 调试打印
        return None
    parkingRecord = ParkingRecord()
    parkingRecord.initByTuple(record)
    return parkingRecord


def query_current_standard(car_type: int) -> ChargeRate:
    '''
    获取某种车型当前的收费标准
    '''
    sql = ('SELECT standard.id,hour_rate,modify_time '
           ' FROM type,standard'
           ' WHERE type.standard=standard.id AND type.id={}'.format(
               car_type))

    result = database.query(sql)
    if len(result) > 0:
        standard = ChargeRate()
        standard.initByTuple(result[0])
        return standard
    else:
        print('查询不到该车型对应的收费标准')  # 调试打印
        return None


def leave_parkinglots(parkingRecord: ParkingRecord) -> (bool, ParkingRecord):
    '''
    离开停车场，将parkingRecord写入数据库
    如果该车已经离开，则失败
    @param parkingRecord:停车记录
    @return 是否成功
    '''
    # 插入离场时间和停车费用，并且修改停车状态 0-已离场，1-正在停车
    sql = "UPDATE record SET status=0,leave_time=%s,cost=%s WHERE id=%s"
    value = (toTimeStr(parkingRecord.leave_time),
             parkingRecord.cost, parkingRecord.record_id)
    rowcount, _ = database.update(sql, value)
    return rowcount > 0


def set_standard(standardDict: dict):
    '''
    设置收费标准
    接收一个字典，字典的键为车型编号，对应的值为该车型每小时的费用
    例如：
    {
        1:5,//1型车每小时收费5元
        2:6.5,//2型车每小时收费6.5元
    }
    @return 是否成功
    '''
    # 检查标准是否改变
    for car_type in standardDict.keys():
        standard = query_current_standard(car_type)  # ChargeRate类型
        if standard.hour_rate != standardDict[car_type]:
            # 若标准改变，则添加一条收费标准，并将当前车型写入数据库
            sql = 'INSERT INTO standard(hour_rate,modify_time,type) VALUES(%s,%s,%s)'
            value = (standardDict[car_type], toTimeStr(time.time()), car_type)
            affected_rows, insert_id = database.update(sql, value)
            if affected_rows <= 0:
                return False
            # 并设置当前车型的收费标准为新的标准
            sql = "UPDATE type SET standard=%s WHERE id=%s"
            value = (insert_id, car_type)
            affected_rows, _ = database.update(sql, value)
            if affected_rows <= 0:
                return False

    return True


def get_current_carport():
    '''
    获取当前停车数量和总停车数量,返回值为两个参数
    @return ((类别1,停车数量),(类别2,停车数量),…    ) 总停车数量
    '''
    sql1 = "select type,count(*) from record where status=1 GROUP BY type"
    # print(database.query(sql1)[0][0])
    curCarport = database.query(sql1)
    sql2 = "select SUM(carport) from type"
    totalCarport = database.query(sql2)[0][0]
    return curCarport, totalCarport


def get_history_record_by_day(day=None, month=None, year=None):
    '''
    获取某天历史停车记录
    @return [符合标准的停车记录]
    '''
    # 设置默认值
    now = datetime.datetime.now()

    if year == None:
        year = now.year
    if month == None:
        month = now.month

    sql = "SELECT * FROM record WHERE AND date_format(leave_time,'%d')={} date_format(leave_time, '%m' ) = {} AND date_format(leave_time,'%Y')={}".format(
        day, month, year)

    # sql = "select * from record WHERE to_days(leave_time) = to_days(now());"
    data = database.query(sql)
    # print(values)

    a = []
    for row in data:
        b = ParkingRecord(record_id=row[0], entry_time=row[1], leave_time=row[2], cost=row[3],
                          licence_plate=row[4], status=row[5], remark=row[6], car_type=row[7], standard=row[8])
        a.append(b)
        b.printRecord()

    return a


def get_history_record_by_week(week=None, year=None):
    '''
    获取某周历史停车记录
    @param week: 这一年的第几周，例如42代表这一年的第42周，默认为当前周在year年的周数
    @param year: 年份，例如2020，默认为今年
    @return [符合标准的停车记录]
    '''
    # 设置默认值
    today = datetime.date.today()

    # isocalendar函数返回值示例： (2018, 1, 4)　　# 结果表示当前日期对应2018年的第1周的第4天，注：假设当前日期是2018年1月4日
    if year == None:
        year = today.isocalendar()[0]
    if week == None:
        week = datetime.date(year, today.month, today.day).isocalendar()[1]
    # 待加入标志位及条件
    sql = "select * from record WHERE WEEKOFYEAR(date_format(leave_time,'%Y-%m-%d')) = {} AND date_format(leave_time,'%Y')={}".format(
        week, year)
    data = database.query(sql)
    # print(values)

    a = []
    for row in data:
        b = ParkingRecord(record_id=row[0], entry_time=row[1], leave_time=row[2], cost=row[3],
                          licence_plate=row[4], status=row[5], remark=row[6], car_type=row[7], standard=row[8])
        a.append(b)
        b.printRecord()

    return a


def get_history_record_by_month(month=None, year=None):
    '''
    获取某月历史停车记录
    @param month: 这一年的第几月，默认为当前月
    @param year: 年份，例如2020，默认为今年
    @return [符合标准的停车记录]
    '''
    # 设置默认值
    now = datetime.datetime.now()

    if year == None:
        year = now.year
    if month == None:
        month = now.month

    sql = "SELECT * FROM record WHERE date_format(leave_time, '%m' ) = {} AND date_format(leave_time,'%Y')={}".format(
        month, year)
    # print(sql)
    data = database.query(sql)
    # print(values)

    a = []
    for row in data:
        b = ParkingRecord(record_id=row[0], entry_time=row[1], leave_time=row[2], cost=row[3],
                          licence_plate=row[4], status=row[5], remark=row[6], car_type=row[7], standard=row[8])
        a.append(b)
        b.printRecord()

    return a


def get_history_record_by_year(year=None):
    '''
    获取某年历史停车记录
    @return [符合标准的停车记录]
    '''
    # 设置默认值
    now = datetime.datetime.now()

    if year == None:
        year = now.year

    sql = "select * from record where date_format(leave_time,'%Y')={}".format(
        year)
    # print(sql)
    data = database.query(sql)
    # print(values)

    a = []
    for row in data:
        b = ParkingRecord(record_id=row[0], entry_time=row[1], leave_time=row[2], cost=row[3],
                          licence_plate=row[4], status=row[5], remark=row[6], car_type=row[7], standard=row[8])
        a.append(b)
        b.printRecord()

    return a


def get_history_record():
    '''
    获取所有历史停车记录
    @return [符合标准的停车记录]
    '''
    sql = "select * from record"
    data = database.query(sql)
    # print(values)

    a = []
    for row in data:
        b = ParkingRecord(record_id=row[0], entry_time=row[1], leave_time=row[2], cost=row[3],
                          licence_plate=row[4], status=row[5], remark=row[6], car_type=row[7], standard=row[8])
        a.append(b)
        # b.printRecord()

    return a


def get_history_record_period(startTime, endTime):
    '''
    获得一段时间内所有类别车型的数量
    @return ([所有车型],[对应一段时间内的数量])
    '''
    data = get_all_car_type()
    carType = []
    carTypeId = []
    amount = []
    for row in data:
        a = row[0]
        b = row[1]
        carTypeId.append(a)  # 向车辆ID列表中新增类型
        carType.append(b)  # 向车辆类别列表中新增类型说明

    for item in carTypeId:
        sql = "select count(*) from record where type='{}' and leave_time>'{}' and leave_time<'{}'".format(
            item, startTime, endTime)
        amount.append(database.query(sql)[0][0])

    return (carType, amount)


def get_history_standard(car_type: int):
    '''
    获取历史收费标准
    @parm car_type 车型
    @return [符合标准的收费记录]
    '''
    sql = 'select * from standard where type={}'.format(car_type)  # 待加入标志位及条件
    data = database.query(sql)
    a = []
    for row in data:
        b = ChargeRate(standard_id=row[0],
                       hour_rate=row[1], modify_time=row[2])
        a.append(b)
        b.printRate()

    return a


def get_history_amount_by_day(day=None, month=None, year=None):
    '''
    获取某天历史停车数量
    @return 符合标准的停车数量
    '''
    # 设置默认值
    now = datetime.datetime.now()

    if year == None:
        year = now.year
    if month == None:
        month = now.month

    sql = "SELECT * FROM record WHERE AND date_format(leave_time,'%d')={} date_format(leave_time, '%m' ) = {} AND date_format(leave_time,'%Y')={}".format(
        day, month, year)

    # sql = "select count(*) from record WHERE to_days(leave_time) = to_days(now());"
    data = database.query(sql)
    # print(values)

    return data[0][0]


def get_history_amount_by_week(week=None, year=None):
    '''
    获取某周历史停车数量
    @param week: 这一年的第几周，例如42代表这一年的第42周，默认为当前周在year年的周数
    @param year: 年份，例如2020，默认为今年
    @return 符合标准的停车数量
    '''
    # 设置默认值
    today = datetime.date.today()

    # isocalendar函数返回值示例： (2018, 1, 4)　　# 结果表示当前日期对应2018年的第1周的第4天，注：假设当前日期是2018年1月4日
    if year == None:
        year = today.isocalendar()[0]
    if week == None:
        week = datetime.date(year, today.month, today.day).isocalendar()[1]
    # 待加入标志位及条件
    sql = "select count(*) from record WHERE WEEKOFYEAR(date_format(leave_time,'%Y-%m-%d')) = {} AND date_format(leave_time,'%Y')={}".format(
        week, year)
    data = database.query(sql)
    # print(values)

    return data[0][0]


def get_history_amount_by_month(month=None, year=None):
    '''
    获取某月历史停车记录
    @param month: 这一年的第几月，默认为当前月
    @param year: 年份，例如2020，默认为今年
    @return 符合标准的停车数量
    '''
    # 设置默认值
    now = datetime.datetime.now()

    if year == None:
        year = now.year
    if month == None:
        month = now.month

    sql = "SELECT count(*) FROM record WHERE date_format(leave_time, '%m' ) = {} AND date_format(leave_time,'%Y')={}".format(
        month, year)
    # print(sql)
    data = database.query(sql)
    # print(values)

    return data[0][0]


def get_history_amount_by_year(year=None):
    '''
    获取某年历史停车数量
    @return 符合标准的停车数量
    '''
    # 设置默认值
    now = datetime.datetime.now()

    if year == None:
        year = now.year

    sql = "select count(*) from record where date_format(leave_time,'%Y')={}".format(
        year)
    # print(sql)
    data = database.query(sql)
    # print(values)

    return data[0][0]


def get_all_car_type():
    '''
    获得所有车型
    @return 返回值示例：((1, '小型车', 11, 200), (2, '大型车', 9, 100), (3, '新能源', 5, 500))
    '''
    sql = 'select * from type'
    return database.query(sql)

# def SqlConnect(sql: str):
#     """
#     函数用于连接和操作数据库，参数为sql语句
#     """
#     conn = mysql.connector.connect(
#         user=database_config['user'], password=database_config['password'], database=database_config['database'])
#     cursor = conn.cursor()
#     # 运行查询:
#     cursor.execute(sql)
#     values = cursor.fetchall()
#     # print(values)
#     # 关闭Cursor和Connection:
#     cursor.close()
#     conn.close()
#     return values


def draw_line_chart(start_x: int, x_span: str, span_count: int):
    '''
    绘制折线图，x轴为时间，y轴为停车数量
    @param start_x:起始坐标，例如x_span为'year'，则start_x为2020,则代表从2020年开始显示
    @param x_span: 横轴跨度，可选值为'year','month','day'
    @param span_count: 跨度数量，需要显示几个跨度，例如显示几年的数据
    @return 保存的图片文件的路径字符串
    '''
    plt.clf()
    x_data = [x for x in range(start_x, start_x+span_count)]

    if x_span == 'year':
        y_data = [get_history_amount_by_year(year) for year in x_data]
    elif x_span == 'month':
        y_data = [get_history_amount_by_month(month) for month in x_data]
    elif x_span == 'day':
        y_data = [get_history_amount_by_day(day) for day in x_data]

    print(x_data)
    print(y_data)
    plt.xlabel('time:{}'.format(x_span))
    plt.ylabel('count')
    plt.plot(x_data, y_data)

    for x, y in zip(x_data, y_data):
        plt.text(x, y, y, ha='center', va='bottom', fontsize=10)

    plt.legend()
    pathName = 'images/{}-{}-{}-lineChart.png'.format(
        start_x, start_x+span_count-1, x_span)
    plt.savefig(pathName)
    return pathName


def draw_pie_chart(start: int, span: str, span_count: int):
    '''
    绘制折线图，内容为不同车型所占的比例
    @param start:起始坐标，例如x_span为'year'，则start_x为2020,则代表从2020年开始显示
    @param span: 横轴跨度，可选值为'year','month','day'
    @param span_count: 跨度数量，需要显示几个跨度，例如显示几年的数据
    @return 保存的图片文件的路径字符串
    '''
    plt.clf()
    # 设置默认值
    today = datetime.date.today()

    start_year = today.year if span != 'year' else start
    start_month = today.month if span != 'month' else start
    start_day = today.day if span != 'day' else start

    start_time = datetime.date(start_year, start_month, start_day)
    if span == 'year':
        end_time = start_time + datetime.timedelta(year=span_count)
    elif span == 'month':
        end_time = start_time + relativedelta(months=span_count)
    elif span == 'day':
        end_time = start_time + datetime.timedelta(day=span_count)

    print(start_time, end_time)

    start_time_str = start_time.strftime('%Y-%m-%d')
    end_time_str = end_time.strftime('%Y-%m-%d')

    # labels, sizes = ['小型车', '大型车', '新能源'], [64, 35, 1]  # 测试数据
    labels, sizes = get_history_record_period(
        start_time_str, end_time_str)  # 获取数据

    print(labels, sizes)
    # labeldistance，文本的位置离远点有多远，1.1指1.1倍半径的位置
    # autopct，圆里面的文本格式，%3.1f%%表示小数有三位，整数有一位的浮点数
    # shadow，饼是否有阴影
    # startangle，起始角度，0，表示从0开始逆时针转，为第一块。一般选择从90度开始比较好看
    # pctdistance，百分比的text离圆心的距离
    # patches, l_texts, p_texts，为了得到饼图的返回值，p_texts饼图内部文本的，l_texts饼图外label的文本
    matplotlib.rcParams['font.sans-serif'] = ['SimHei']  # 改字体，防止乱码
    patches, l_text, p_text = plt.pie(sizes, labels=labels,
                                      labeldistance=1.1, autopct='%2.0f%%', shadow=False,
                                      startangle=90, pctdistance=0.6)

    # 改变文本
    # 方法是把每一个text遍历。
    for i, text in enumerate(p_text):
        text.set_text('{}({})'.format(text.get_text(), sizes[i]))

    # 设置x，y轴刻度一致，这样饼图才能是圆的
    plt.axis('equal')
    plt.legend(loc='upper left', bbox_to_anchor=(-0.1, 1))
    # loc: 表示legend的位置，包括'upper right','upper left','lower right','lower left'等
    # bbox_to_anchor: 表示legend距离图形之间的距离，当出现图形与legend重叠时，可使用bbox_to_anchor进行调整legend的位置
    # 由两个参数决定，第一个参数为legend距离左边的距离，第二个参数为距离下面的距离
    plt.grid()

    # plt.show()
    plt.legend()
    pathName = 'images/{}-{}-{}-pieChart.png'.format(
        start, start+span_count-1, span)
    plt.savefig(pathName)
    return pathName


if __name__ == "__main__":
    # # 查询车牌对应的停车记录
    # parkingRecord = query_record_by_plate('陕A666666')
    # # 计算停车费用
    # hour_rate = query_current_standard(parkingRecord.car_type).hour_rate
    # # 此方法内部已经把费用和离场时间写入parkingRecord对象中了，也可以用变量接收返回值
    # cost = parkingRecord.calculate_cost(hour_rate)

    # leave_parkinglots(parkingRecord)

    # set_standard({1: 2, 2: 10})

    # print(get_all_car_type())

    # records = get_history_record_by_month(month=10, year=2019)
    # for record in records:
    #     record.printRecord()
    draw_pie_chart(1, 'month', 12)
    draw_line_chart(1, 'month', 12)

    pass
